<?php
function migrateReviews()
{
    $db = db::$db;

    $sql = "DROP TABLE IF EXISTS reviews";
    $db->exec($sql);

    $sql = "CREATE TABLE IF NOT EXISTS reviews (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id INT(6) UNSIGNED NOT NULL,
        route_id INT(6) UNSIGNED NOT NULL,
        title VARCHAR(100) NOT NULL,
        rev_text TEXT NOT NULL,
        rating FLOAT NOT NULL,
        rev_date DATE NOT NULL,
        public_state BOOLEAN NOT NULL DEFAULT false,
        review_type INT(6) UNSIGNED NOT NULL DEFAULT 2,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";

    $db->exec($sql);
}



function _saveReview()
{
    $db = db::$db;
    $userId = (int) verifyToken(Req::$data['token'] ?? 0);

    $id = (int) (Req::$data['id'] ?? 0);
    $routeId = (int) (Req::$data['route_id'] ?? 0);
    $title = Req::$data['title'] ?? '';
    $revText = Req::$data['rev_text'] ?? '';
    $rating = (float) (Req::$data['rating'] ?? 0);
    $revDate = Req::$data['rev_date'] ?? date('Y-m-d');
    $publicState = (bool) (Req::$data['public_state'] ?? false);

    try {
        if ($id > 0) {
            // Обновление существующего отзыва
            $sql = "UPDATE reviews SET route_id = ?, title = ?, rev_text = ?, rating = ?, rev_date = ?, public_state = ? WHERE id = ? AND user_id = ?";
            $stmt = $db->prepare($sql);
            $stmt->execute([$routeId, $title, $revText, $rating, $revDate, $publicState, $id, $userId]);
        } else {
            // Создание нового отзыва
            $sql = "INSERT INTO reviews (user_id, route_id, title, rev_text, rating, rev_date, public_state) VALUES (?, ?, ?, ?, ?, ?, ?)";
            $stmt = $db->prepare($sql);
            $stmt->execute([$userId, $routeId, $title, $revText, $rating, $revDate, $publicState]);
            $id = $db->lastInsertId();
        }

       return ['id'=>$id];
    } catch (PDOException $e) {
        return null;
    }
}

function setUserReviewPublicStateById()
{

    $db = db::$db;
    $userId = (int) verifyToken(Req::$data['token'] ?? 0);
    $reviewId = (int) Req::$data['id'];
    $publicState = (bool) Req::$data['public_state'];
    $sql = "UPDATE reviews SET public_state = ? WHERE id = ? AND user_id = ?";
    $stmt = $db->prepare($sql);
    return $stmt->execute([$publicState, $reviewId, $userId]);
}

function _getUserReviews()
{
    $db=db::$db;
    $userId = (int) verifyToken(Req::$data['token']??0);

    $stmt = $db->prepare("
        SELECT r.*, rt.name as route_name
        FROM reviews r
        JOIN routes rt ON r.route_id = rt.id
        WHERE r.user_id = ?
        ORDER BY r.created_at DESC
    ");

    $stmt->execute([$userId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function _getUserReviewById()
{
    $db=db::$db;
    $userId = (int) verifyToken(Req::$data['token']??0);
    $reviewId = (int) Req::$data['id'];

    $stmt = $db->prepare("
        SELECT r.*, rt.name as route_name
        FROM reviews r
        JOIN routes rt ON r.route_id = rt.id
        WHERE r.user_id = ? AND r.id = ?
        LIMIT 1
    ");

    $stmt->execute([$userId, $reviewId]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function getAllReviews()
{
    $db=db::$db;
    $stmt = $db->prepare("
        SELECT r.*, rt.name as route_name
        FROM reviews r
        JOIN routes rt ON r.route_id = rt.id
        ORDER BY r.created_at DESC
    ");
    $stmt->execute();
    return ['reviews' => $stmt->fetchAll(PDO::FETCH_ASSOC) ];
}